package com.example.model;

import android.content.ContentResolver;
import android.content.ContentValues;
import android.content.Context;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.ContactsContract.Contacts;
import android.widget.Toast;

public class HistoryModel extends SQLiteOpenHelper {
	private final static String DATABASE_NAME = "HISTORY.db";
	private final static int DATABASE_VERSION = 1;
	private final static String TABLE_NAME_HIS = "histroy_table";
	public final static String HIS_ID = "history_id";
	public final static String HIS_URL = "history_url";
	public final static String HIS_TIME = "history_time";
	public final static String HIS_TIME_FRAME = "history_timeframe";
	public final static String HIS_LOC = "history_loc";
	public final static String HIS_AT = "history_asstimes";

	public final static String DATATYPE_TEXT = " TEXT";
	public final static String DATATYPE_INTEGER = " INTEGER";
	public final static String COMMA_SEP = ",";
	public final static String INSERT_QUERY = "CREATE TABLE " + TABLE_NAME_HIS
			+ "(" + HIS_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + HIS_URL
			+ DATATYPE_TEXT + COMMA_SEP + HIS_TIME + DATATYPE_TEXT + COMMA_SEP
			+ HIS_TIME_FRAME + DATATYPE_TEXT + COMMA_SEP + HIS_LOC
			+ DATATYPE_TEXT + COMMA_SEP + HIS_AT + DATATYPE_INTEGER + ")";

	public HistoryModel(Context context) {
		// TODO Auto-generated constructor stub
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	// 创建table
	@Override
	public void onCreate(SQLiteDatabase db) {
		// String sql = "CREATE TABLE " + TABLE_NAME_HIS + " (" + HIS_ID
		// + " INTEGER primary key autoincrement, " + HIS_URL + " text, "
		// + HIS_TIME + " text," + HIS_LOC + "text,"
		// + HIS_AT + "INTEGER);";
		String sql = INSERT_QUERY;
		db.execSQL(sql);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		String sql = "DROP TABLE IF EXISTS " + TABLE_NAME_HIS;
		db.execSQL(sql);
		onCreate(db);
	}

	public Cursor select() {
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.query(TABLE_NAME_HIS, null, null, null, null, null,
				null);

		// Test code start.
		System.out.println("Cursor rows amount: " + cursor.getCount());
		// Test cod end.

		// db.close();
		return cursor;
	}

	public Cursor sort(String timeFrame, String location) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor;
	
	
		
		String columns[] = { HIS_ID,HIS_URL, HIS_TIME, HIS_TIME_FRAME, HIS_LOC, HIS_AT};
		String selection =  HIS_TIME_FRAME + "=?"+ " and " + HIS_LOC + "=?";
		String selectionArgs[] = { timeFrame,location };
		String sort=HIS_AT+" DESC";

		 cursor = db.query(TABLE_NAME_HIS, columns, selection, selectionArgs, null, null,
				sort,null);
	   

		return cursor;

	}
	public Cursor fav(String timeFrame, String location) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor;
	
	
		
		String columns[] = { HIS_ID,HIS_URL, HIS_TIME, HIS_TIME_FRAME, HIS_LOC, HIS_AT};
		String selection =  HIS_TIME_FRAME + "=?"+ " and " + HIS_LOC + "=?";
		String selectionArgs[] = { timeFrame,location };
		String sort=HIS_AT+" DESC";

		 cursor = db.query(TABLE_NAME_HIS, columns, selection, selectionArgs, null, null,
				sort,"1");
	    

		return cursor;

	}

	// 增加�?作
	public long insert(String url, String time, String timeFrame,
			String location, int accessedTimes) {

		SQLiteDatabase db = this.getWritableDatabase();
		/* ContentValues */
		ContentValues cv = new ContentValues();
		cv.put(HIS_URL, url);
		cv.put(HIS_TIME, time);
		cv.put(HIS_TIME_FRAME, timeFrame);
		cv.put(HIS_LOC, location);
		cv.put(HIS_AT, accessedTimes);

		long row = db.insert(TABLE_NAME_HIS, null, cv);
		return row;
	}

	// 删除�?作
	public void delete(int id) {
		SQLiteDatabase db = this.getWritableDatabase();
		String where = HIS_ID + " = ?";
		String[] whereValue = { Integer.toString(id) };
		db.delete(TABLE_NAME_HIS, where, whereValue);
	}

	// 修改�?作
	public void update(int id, String url, String time, String timeFrame,
			String location, int accessedTimes) {
		SQLiteDatabase db = this.getWritableDatabase();
		String where = HIS_ID + " = ?";
		String[] whereValue = { Integer.toString(id) };

		ContentValues cv = new ContentValues();
		cv.put(HIS_URL, url);
		cv.put(HIS_TIME, time);
		cv.put(HIS_TIME_FRAME, timeFrame);
		cv.put(HIS_LOC, location);
		cv.put(HIS_AT, accessedTimes);
		db.update(TABLE_NAME_HIS, cv, where, whereValue);
	}

	public String query_id(String url, String timeFrame, String location) {
		SQLiteDatabase db = this.getWritableDatabase();

		String columns[] = { HIS_ID, HIS_URL, HIS_TIME, HIS_TIME_FRAME,
				HIS_LOC, HIS_AT };
		String selection = HIS_URL + "=?" + " and " + HIS_TIME_FRAME + "=?"
				+ " and " + HIS_LOC + "=?";
		String selectionArgs[] = { url, timeFrame, location };

		Cursor result = db.query(TABLE_NAME_HIS, columns, selection,
				selectionArgs, null, null, null);

		int resultCount = result.getCount();
		if (resultCount == 0 || !result.moveToFirst()) {

			System.out.println("No such row !!!!!!!!!!!!!!!");

			return null;
		}
	
		String id = result.getString(result.getColumnIndex(HIS_ID));
		
		return id;
	}
	public String query_at(String url, String timeFrame, String location) {
		SQLiteDatabase db = this.getWritableDatabase();

		String columns[] = { HIS_ID, HIS_URL, HIS_TIME, HIS_TIME_FRAME,
				HIS_LOC, HIS_AT };
		String selection = HIS_URL + "=?" + " and " + HIS_TIME_FRAME + "=?"
				+ " and " + HIS_LOC + "=?";
		String selectionArgs[] = { url, timeFrame, location };

		Cursor result = db.query(TABLE_NAME_HIS, columns, selection,
				selectionArgs, null, null, null);

		int resultCount = result.getCount();
		if (resultCount == 0 || !result.moveToFirst()) {

			System.out.println("No such row !!!!!!!!!!!!!!!");

			return null;
		}
	
		String ath = result.getString(result.getColumnIndex(HIS_AT));
		System.out.printf("aaaaaaaaaaaaaaaaaaa",ath);
		return ath;
	}
	

	public String query_url( String timeFrame,String location) {
		SQLiteDatabase db = this.getWritableDatabase();

		String columns[] = { HIS_ID, HIS_URL, HIS_TIME, HIS_TIME_FRAME,
				HIS_LOC, HIS_AT };
		String selection =  HIS_TIME_FRAME + "=?"+ " and " + HIS_LOC + "=?";
		String selectionArgs[] = { timeFrame, location };
		String sort=HIS_AT+" DESC";//..............

		//Cursor result = db.query(TABLE_NAME_HIS, columns, selection,
		//		selectionArgs, null, null, null);
		Cursor result = db.query(TABLE_NAME_HIS, columns, selection,
				selectionArgs, null,null, sort, "1");

		int resultCount = result.getCount();
		if (resultCount == 0 || !result.moveToFirst()) {

			System.out.println("No url^^ !!!!!!!!!!!!!!!");

			return null;
		}
		else
		{

		String hisurl = result.getString(result.getColumnIndex(HIS_URL));
		System.out.println("hisurl...........................: " + hisurl);

		return hisurl ;
		}
	}
}